package com.cmnit.analysis.common

import java.sql.{Connection, PreparedStatement, SQLException}

import com.cmnit.analysis.util.{ConfigurationManager, DataSourceUtil, EnvUtil}
import org.apache.log4j.Logger
import org.apache.spark.sql.DataFrame

trait TSql {
  private val logger: Logger = Logger.getLogger(this.getClass)

  /**
   * 读取数据并显示
   *
   * @param tableName 读取表名
   * @param acctName  时间字段
   * @param acctDate  查询日期
   * @return
   */
  def read(tableName: String, acctName: String, acctDate: String): Unit = {
    EnvUtil.take().read
      .format("jdbc")
      .option("dbtable", tableName)
      .option("driver", ConfigurationManager.getProperty("vertica.driver"))
      .option("url", ConfigurationManager.getProperty("vertica.url"))
      .option("username", ConfigurationManager.getProperty("vertica.username"))
      .option("password", ConfigurationManager.getProperty("vertica.password"))
      .load
      .where(acctName + "='" + acctDate + "'")
      .show(30)
  }

  /**
   * 写入数据
   *
   * @param dataFrame 结果数据集
   */
  def write(tableName: String, dataFrame: DataFrame): Unit = {
    dataFrame.write
      .format("jdbc")
      .mode("append")
      .option("dbtable", tableName)
      .option("driver", ConfigurationManager.getProperty("vertica.driver"))
      .option("url", ConfigurationManager.getProperty("vertica.url"))
      .option("username", ConfigurationManager.getProperty("vertica.username"))
      .option("password", ConfigurationManager.getProperty("vertica.password"))
      .save
    logger.info(tableName + "插入完毕！")
  }

  /**
   * 删除数据
   *
   * @param tableName 表名
   * @param acctName  时间字段
   * @param acctDate  账期
   * @return
   */
  def delete(tableName: String, acctName: String, acctDate: String): Unit = {
    val sql = "delete from " + tableName + " where " + acctName + " = '" + acctDate + "'"
    var pstmt: PreparedStatement = null
    var connect: Connection = null
    try {
      connect = DataSourceUtil.getConnection
      pstmt = connect.prepareStatement(sql)
      pstmt.execute
      logger.info("删除" + tableName + "表的账期：" + acctDate)
    } catch {
      case e: SQLException => e.printStackTrace()
    } finally {
      DataSourceUtil.closeResource(pstmt, connect)
    }
  }
}
